﻿Public Class DespachateDAO
    Dim objAcessoDados As AcessoDados = New AcessoDados(Config.StringConexao)

    Public Function ListaTodos() As List(Of Despachante)
        Dim registros As DataSet = New DataSet
        registros = objAcessoDados.ExecutaSqlRetorno("select * from tb_despachante")
        Return CriarListaDesp(registros)
    End Function

    Public Function CriarListaDesp(ByVal Registros As DataSet) As List(Of Despachante)
        Dim lista As New List(Of Despachante)
        Dim objDesp As Despachante
        For Each dr As DataRow In Registros.Tables(0).Rows
            objDesp = CriarDesp(dr)
            lista.Add(objDesp)
        Next
        Return lista
    End Function
    Public Function CriarDesp(ByVal row As DataRow) As Despachante
        Dim objDesp As New Despachante
        Dim objCidade As New Cidade
        Dim objCidadeDao As New CidadeDAO

        objDesp.CodDesp = CInt(row.Item("cod_desp"))
        objDesp.FantasiaDesp = CStr(row.Item("fantasia_desp"))
        objDesp.EmailDesp = CStr(row.Item("email_desp"))
        objDesp.TelDesp = CStr(row.Item("tel_desp"))
        objDesp.EndDesp = CStr(row.Item("end_desp"))
        objDesp.CpfCnpjDesp = CStr(row.Item("cpf_cnpj_desp"))
        objDesp.SenhaDesp = CStr(row.Item("senha_desp"))
        objDesp.StatusDesp = CStr(row.Item("status_desp"))
        objCidade = objCidadeDao.SelectCidadeId(CInt(row.Item("fk_cod_cid")))
        objDesp.Cidade = objCidade

        Return objDesp
    End Function

    Public Function Inserir(ByVal iDesp As Despachante) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder
        sql.Append("insert into tb_despachante (")
        sql.Append(" fantasia_desp, ")
        sql.Append(" email_desp, ")
        sql.Append(" tel_desp, ")
        sql.Append(" end_desp, ")
        sql.Append(" cpf_cnpj_desp, ")
        sql.Append(" senha_desp, ")
        sql.Append(" status_desp, ")
        sql.Append(" fk_cod_cid ")
        sql.Append(") values (")
        sql.Append("'" & iDesp.FantasiaDesp.ToString & "',")
        sql.Append("'" & iDesp.EmailDesp.ToString & "',")
        sql.Append("'" & iDesp.TelDesp.ToString & "',")
        sql.Append("'" & iDesp.EndDesp.ToString & "',")
        sql.Append("'" & iDesp.CpfCnpjDesp.ToString & "',")
        sql.Append("'" & iDesp.SenhaDesp.ToString & "',")
        sql.Append("'" & iDesp.StatusDesp.ToString & "',")
        sql.Append("" & iDesp.Cidade.CodCid & ")")
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function

    Public Function LoginDesp(email As String, senha As String) As Despachante
        Dim objDesp As Despachante
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_despachante ")
        sql.Append(strWhere & "email_desp = " & "'" & email.ToString & "'")
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objAdmData As DataRow = registro.Tables(0).Rows(0)
            objDesp = CriarDesp(objAdmData)
            If objDesp.SenhaDesp = senha Then
                Return objDesp
            End If
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function SelectDespId(ByVal cod_desp As Integer) As Despachante
        Dim objDesp As Despachante
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_despachante ")
        sql.Append(strWhere & "cod_desp = " & cod_desp)
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objDespData As DataRow = registro.Tables(0).Rows(0)
            objDesp = CriarDesp(objDespData)
            Return objDesp
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function SelectDespfantasia(ByVal fantasia_desp As String) As Despachante
        Dim objDesp As Despachante
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_despachante ")
        sql.Append(strWhere & "fantasia_desp = '" & fantasia_desp.ToString & "'")
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objDespData As DataRow = registro.Tables(0).Rows(0)
            objDesp = CriarDesp(objDespData)
            Return objDesp
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function AtualizarStatus(ByVal desp As Despachante) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder
        sql.Append("update tb_despachante set ")
        sql.Append("status_desp = '" & desp.StatusDesp.ToString & "'")
        sql.Append("where cod_desp = " & desp.CodDesp)
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function


    Public Function Atualizar(ByVal desp As Despachante) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder
        sql.Append("update tb_despachante set ")
        sql.Append("fantasia_desp = '" & desp.FantasiaDesp.ToString & "', ")
        sql.Append("email_desp = '" & desp.EmailDesp.ToString & "', ")
        sql.Append("tel_desp = '" & desp.TelDesp.ToString & "',")
        sql.Append("end_desp = '" & desp.EndDesp.ToString & "',")
        sql.Append("cpf_cnpj_desp = '" & desp.CpfCnpjDesp.ToString & "',")
        sql.Append("senha_desp = '" & desp.SenhaDesp.ToString & "',")
        sql.Append("status_desp = '" & desp.StatusDesp.ToString & "',")
        sql.Append("fk_cod_cid = " & desp.Cidade.CodCid & " ")
        sql.Append("where cod_desp = " & desp.CodDesp)
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function



End Class
